﻿using AutoMapper;
using HongYeSystemWebApi.Templet.Entity.ApiResult;
using HongYeSystemWebApi.Templet.Entity.Entities;
using HongYeSystemWebApi.Templet.Entity.Request;
using HongYeSystemWebApi.Templet.Entity.Request.SysProject;
using HongYeSystemWebApi.Templet.Entity.Response;
using HongYeSystemWebApi.Templet.Entity.Response.Project;
using HongYeSystemWebApi.Templet.Entity.Response.SysCostType;
using HongYeSystemWebApi.Templet.Entity.Response.SysExpenditure;
using HongYeSystemWebApi.Templet.IService;
using MySqlX.XDevAPI.Common;
using OfficeOpenXml;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HongYeSystemWebApi.Templet.Service
{
    public class ProjectService : IProjectService
    {
        private readonly SqlSugarClient _db;
        private readonly IMapper _mapper;
        private readonly CommandService _comm;
        /// <summary>
        /// 构造函数依赖注入
        /// </summary>
        /// <param name="db"></param>
        public ProjectService(IHelpDB db, IMapper mapper)
        {
            this._db = db.OpenDB();
            _mapper = mapper;
            _comm = new CommandService();
        }
        public async Task DeleteAsync(MultiIdRequest request)
        {
            List<ProjectEntity> DelData = new List<ProjectEntity>();
            foreach (var item in request.Ids)
            {
                var project = await _db.Queryable<ProjectEntity>().FirstAsync(a => a.Id == item);
                project.Delete = true;
                project.OperatDate = DateTime.Now;
                DelData.Add(project);
            }
            await _db.Updateable(DelData).WhereColumns(it => new { it.Id }).ExecuteCommandAsync();
        }



        public async Task InsertAsync(SysProjectInsertRequest request)
        {
            var data = _mapper.Map<ProjectEntity>(request);
            var dataFir = await _db.Queryable<ProjectEntity>().FirstAsync(X => X.ProjectNumber == request.ProjectNumber && !X.Delete);
            if (dataFir != null)
            {
                throw new Exception($"项目：{dataFir.ProjectName}以占用当前编号");
            }
            data.OperatDate = DateTime.Now;
            await _db.Insertable(data).ExecuteCommandAsync();
        }


        public async Task UpdateAsync(SysProjectUpdataRequest request)
        {
            var dataFir = await _db.Queryable<ProjectEntity>().FirstAsync(X => X.Id == request.Id);
            if (dataFir == null)
            {
                throw new Exception("未找到修改原始数据");
            }
            var data = _mapper.Map<ProjectEntity>(request);
            data.OperatDate = DateTime.Now;
            await _db.Updateable(data).WhereColumns(it => new { it.Id }).ExecuteCommandAsync();
        }

        public async Task<PageModel<SysProjectDataPageResponse>> GetListAsync(SysProjectSelectRequest request)
        {
            var data = _db.Queryable<ProjectEntity>().Where(a => !a.Delete);

            if (!string.IsNullOrEmpty(request.Key))
            {
                data = data.Where(a => (a.ProjectName.Contains(request.Key) || a.ProjectNumber.Contains(request.Key)));
            }
            if (request.StartTime != null)
            { 
                data = data.Where(a=>a.CreateDate >= request.StartTime);
            }
            if (request.EndTime != null)
            { 
                data = data.Where(a=>a.CreateDate <= request.EndTime);
            }
            if (!request.PartyA.Equals(0))
            { 
                data = data.Where(a=>a.PartyA.Equals(request.PartyA));
            }
            RefAsync<int> Total = 0;
            var resultpage = await data.OrderBy(x => x.ProjectNumber, OrderByType.Desc).OrderBy(x => x.Id, OrderByType.Desc).ToPageListAsync(request.PageNumber, request.PageSize, Total);
            var result = _mapper.Map<List<SysProjectDataPageResponse>>(resultpage);
            return new PageModel<SysProjectDataPageResponse>() { Data = result, Total = Total };
        }

        public async Task<ApiResult> ExportByGetListAsync(SysProjectSelectRequest request)
        {
            request.PageSize = int.MaxValue;
            request.PageNumber = 1;
            var result = GetListAsync(request);
            var Suppliers = await _db.Queryable<Supplier>().ToListAsync();
            var ComPanyList = await _db.Queryable<CompanyEntity>().ToListAsync();
            string currentPath = System.IO.Directory.GetCurrentDirectory();
            var datepath = DateTime.Now.ToString("yyyyMMddHHmmss");
            var fileNamepath = Path.Combine("ExcelFile", $"{datepath}Project.xlsx");
            var filePath = Path.Combine(currentPath, fileNamepath);
            var dirPath = Path.Combine(currentPath, "ExcelFile");
            List<List<string>> data = new List<List<string>>();
            var Title = "序号,项目,开始时间,项目编号,甲方,立项单位(部门/公司)".Split(',').ToList();
            data.Add(Title);
            var indexNumber = 1;
            foreach (var item in result.Result.Data)
            {
                var val = new List<string>();
                val.Add(indexNumber.ToString());
                val.Add(item.ProjectName);
                val.Add(item.CreateDate.ToString("yyyy-MM-dd"));
                val.Add(item.ProjectNumber);
                val.Add(Suppliers.FirstOrDefault(x => x.Id == item.PartyA).Name);
                val.Add(ComPanyList.FirstOrDefault(x=>x.Id == item.CompanyId)?.CompanyName);
                indexNumber++;
                data.Add(val);
            }
            ExcelPackage.LicenseContext = LicenseContext.Commercial;
            // 创建一个Excel文件
            FileInfo existingFile = new FileInfo(filePath);
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

                for (int i = 0; i < data.Count; i++)
                {
                    int index = 0;
                    for (char column = 'A'; column <= Convert.ToChar(_comm.GetColumnLetter(Title.Count)); column++)
                    {
                        if (index < data[i].Count)
                        {
                            var datavalue = data[i][index] == null ? "" : data[i][index];
                            var cells = column.ToString() + (i + 1) + "";
                            worksheet.Cells[cells].Value = datavalue;
                            index++;
                        }
                    }
                }

                var cloums = _comm.GetColumnLetter(Title.Count).ToString() + (data.Count ).ToString();
                // 设置边框
                worksheet.Cells[$"A1:{cloums}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells[$"A1:{cloums}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells[$"A1:{cloums}"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells[$"A1:{cloums}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells.AutoFitColumns(); // 对整个工作表自适应
                worksheet.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // 水平居中
                worksheet.Cells.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; // 垂直居中
                // 保存修改后的Excel文件
                package.Save();
                _comm.DeleteFile(dirPath);
            }
            return ApiResult.True(fileNamepath);
        }

        public async Task<ApiResult> ExportAll()
        {
            SysProjectSelectRequest request = new SysProjectSelectRequest();
            request.StartTime = null;
            request.EndTime = null;
            request.PartyA = 0;
            request.PageSize = int.MaxValue;
            request.PageNumber = 1;
            var result = await ExportByGetListAsync(request);
            return result;
        }
    }
}
